/*==============================================================*/
/* DBMS name:      Microsoft SQL Server 2012                    */
/* Created on:     2021/11/17 11:16:54                          */
/*==============================================================*/


if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('DepartmentInfo') and o.name = 'FK_DEPARTME_REFERENCE_DEGGRADE')
alter table DepartmentInfo
   drop constraint FK_DEPARTME_REFERENCE_DEGGRADE
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EmployeeInfo') and o.name = 'FK_EMPLOYEE_REFERENCE_DEPARTME')
alter table EmployeeInfo
   drop constraint FK_EMPLOYEE_REFERENCE_DEPARTME
go

if exists (select 1
   from sys.sysreferences r join sys.sysobjects o on (o.id = r.constid and o.type = 'F')
   where r.fkeyid = object_id('EmployeeInfo') and o.name = 'FK_EMPLOYEE_REFERENCE_DEGGRADE')
alter table EmployeeInfo
   drop constraint FK_EMPLOYEE_REFERENCE_DEGGRADE
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DegGrade')
            and   type = 'U')
   drop table DegGrade
go

if exists (select 1
            from  sysobjects
           where  id = object_id('DepartmentInfo')
            and   type = 'U')
   drop table DepartmentInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('EmployeeInfo')
            and   type = 'U')
   drop table EmployeeInfo
go

if exists (select 1
            from  sysobjects
           where  id = object_id('UserInfo')
            and   type = 'U')
   drop table UserInfo
go

/*==============================================================*/
/* Table: DegGrade                                              */
/*==============================================================*/
create table DegGrade (
   id                   int                  identity,
   DegId                nvarchar(80)         null,
   Grade                nvarchar(80)         null,
   constraint PK_DEGGRADE primary key (id)
)
go

/*==============================================================*/
/* Table: DepartmentInfo                                        */
/*==============================================================*/
create table DepartmentInfo (
   id                   int                  identity,
   DegId                nvarchar(80)         not null,
   DegName              nvarchar(80)         null,
   DegCustodian         nvarchar(80)         null,
   phone                nvarchar(80)         null,
   Email                nvarchar(80)         null,
   constraint PK_DEPARTMENTINFO primary key (id)
)
go

/*==============================================================*/
/* Table: EmployeeInfo                                          */
/*==============================================================*/
create table EmployeeInfo (
   id                   int                  not null,
   Deg_id               int                  null,
   WorkId               nvarchar(80)         null,
   Name                 nvarchar(80)         null,
   Grade                nvarchar(80)         null,
   Phone                nvarchar(80)         null,
   Pay                  nvarchar(80)         null,
   constraint PK_EMPLOYEEINFO primary key (id)
)
go

/*==============================================================*/
/* Table: UserInfo                                              */
/*==============================================================*/
create table UserInfo (
   UserId               int                  not null,
   UserName             nvarchar(80)         null,
   CardNum              nvarchar(80)         null,
   Phone                nvarchar(80)         null,
   Address              nvarchar(80)         null,
   constraint PK_USERINFO primary key (UserId)
)
go

alter table DepartmentInfo
   add constraint FK_DEPARTME_REFERENCE_DEGGRADE foreign key (id)
      references DegGrade (id)
go

alter table EmployeeInfo
   add constraint FK_EMPLOYEE_REFERENCE_DEPARTME foreign key (id)
      references DepartmentInfo (id)
go

alter table EmployeeInfo
   add constraint FK_EMPLOYEE_REFERENCE_DEGGRADE foreign key (Deg_id)
      references DegGrade (id)
go

